Introduction to Cursors and Row-by-Row Processing in MySQL

Course: MySQL Training

Topic: Introduction to Cursors and Row-by-Row Processing

Course Link: MySQL Training Course

Demo Link: Register for a Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction to Cursors in MySQL

A cursor is a database programming feature that allows a stored program to retrieve and process rows from a query result one row at a time. In MySQL, cursors are commonly used inside stored procedures when individual records require specific business logic or sequential processing.

SQL is primarily designed for set-based operations. For example, a single UPDATE statement can modify many records simultaneously. However, certain business requirements involve checking each record individually, applying different conditions, performing sequential calculations, or recording a separate processing result for each row. Cursors can help implement these requirements.

A cursor provides a controlled way to access the rows returned by a SELECT query. The standard workflow includes declaring the cursor, opening it, fetching rows into variables, processing those values, detecting the end of the result set, and closing the cursor.

1.1 What Is Row-by-Row Processing?

Row-by-row processing means handling each database record individually rather than applying one operation to an entire set of records.

For example, a company may need to evaluate every employee's salary according to different conditions. A cursor can fetch the first employee, evaluate the salary, move to the next employee, and repeat the operation until all selected employees have been processed.

Fetch Employee 1
    ↓
Process Employee 1
    ↓
Fetch Employee 2
    ↓
Process Employee 2
    ↓
Continue Until No Rows Remain

1.2 What Is a Result Set?

A result set is the collection of rows returned by a database query. A cursor allows a stored program to fetch these rows sequentially.

SELECT employee_id, employee_name, salary
FROM employees
WHERE department_id = 10;

If this query returns several employees, a cursor can retrieve each employee's ID, name, and salary into variables for further processing.

2. Learning Objectives

After completing this topic, learners should be able to:

• Understand the purpose of cursors in MySQL.
• Explain row-by-row processing and its applications.
• Identify cursor characteristics and limitations.
• Declare and open a cursor inside a stored procedure.
• Fetch query results into local variables.
• Use a NOT FOUND handler to detect the end of a result set.
• Process individual records using loops and conditional statements.
• Close cursors after completing processing.
• Identify common cursor errors and troubleshooting techniques.
• Compare cursor-based processing with set-based SQL operations.

3. Why Are Cursors Needed?

3.1 Individual Record Processing

Sometimes, different records require different actions. For example, employees with low salaries may need a review, while employees with higher salaries may be classified into another category.

A cursor can fetch each employee's details and apply an IF or ELSEIF condition to determine the appropriate action.

3.2 Complex Business Rules

Some business rules require several decisions for every selected record. A cursor can make such procedural logic easier to organize when a set-based SQL expression is not practical.

3.3 Sequential Calculations

A cursor can be useful when processing a row requires information calculated from earlier rows. The query should specify an ORDER BY clause whenever the order of processing is important.

3.4 Record-Level Logging

Some applications need to record a separate processing status for each record. A cursor can evaluate individual rows and insert their outcomes into an audit or processing table.

3.5 Data Migration and Validation

During data migration, some records may require individual validation, transformation, or error reporting. Cursors can support these workflows, although bulk operations and staging tables should be considered first for efficiency.

4. Characteristics of MySQL Cursors

MySQL stored-program cursors have specific properties that distinguish them from some cursor implementations in other database systems.

5. Main Components of a Cursor

A standard cursor implementation uses four main statements: DECLARE, OPEN, FETCH, and CLOSE. A condition handler is commonly added to detect when no more rows are available.

5.1 DECLARE CURSOR

The DECLARE CURSOR statement defines the SELECT query that supplies the rows to process.

DECLARE employee_cursor CURSOR FOR
    SELECT employee_id, employee_name, salary
    FROM employees;

The cursor is named employee_cursor. Its SELECT query defines which columns and rows can be fetched.

5.2 OPEN CURSOR

The OPEN statement opens the declared cursor and prepares it for fetching.

OPEN employee_cursor;

A cursor must be opened before the stored procedure can fetch rows from it.

5.3 FETCH

FETCH retrieves the next available row and places its column values into the specified variables.

FETCH employee_cursor
INTO v_employee_id, v_employee_name, v_salary;

The number and order of variables must match the columns selected by the cursor query. The receiving variables should also have suitable data types.

5.4 CLOSE CURSOR

The CLOSE statement closes an opened cursor after its rows have been processed.

CLOSE employee_cursor;

Closing a cursor is an important cleanup step. Close it when processing is complete.

5.5 NOT FOUND Handler

A NOT FOUND handler detects the condition in which a FETCH operation has no more rows to return. A common approach is to set a Boolean flag.

DECLARE v_done BOOLEAN DEFAULT FALSE;

DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET v_done = TRUE;

When the end of the result set is reached, the handler sets v_done to TRUE. The loop checks the flag and exits instead of processing a nonexistent row.

6. Basic Cursor Syntax

The following example demonstrates the overall structure of a cursor-based stored procedure.

DELIMITER //

CREATE PROCEDURE process_employees()
BEGIN
    DECLARE v_employee_id INT;
    DECLARE v_employee_name VARCHAR(100);
    DECLARE v_done BOOLEAN DEFAULT FALSE;

    DECLARE employee_cursor CURSOR FOR
        SELECT employee_id, employee_name
        FROM employees
        ORDER BY employee_id;

    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET v_done = TRUE;

    OPEN employee_cursor;

    employee_loop: LOOP
        FETCH employee_cursor
        INTO v_employee_id, v_employee_name;

        IF v_done THEN
            LEAVE employee_loop;
        END IF;

        -- Process the current employee here.
        SELECT v_employee_id AS employee_id,
               v_employee_name AS employee_name;
    END LOOP;

    CLOSE employee_cursor;
END //

DELIMITER ;

Important: In a MySQL stored-program block, local variables and conditions must be declared before cursors, and cursor declarations must appear before handler declarations. Executable statements follow the declarations.

7. Step-by-Step Cursor Execution

1. Declare variables: Create variables to receive the query's column values.
2. Declare the cursor: Specify the SELECT query whose rows will be processed.
3. Declare the handler: Define how the program handles the end of the result set.
4. Open the cursor: Prepare the cursor for fetching.
5. Fetch a row: Retrieve the next row into the variables.
6. Check the completion flag: Exit the loop if no row was fetched.
7. Process the row: Apply the required business logic.
8. Repeat the process: Continue fetching until all rows have been processed.
9. Close the cursor: Finish processing and close the cursor.

8. Preparing a Sample Database

Create a database and an employee table to practice cursor operations.

CREATE DATABASE cursor_practice;
USE cursor_practice;

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INT,
    salary DECIMAL(10,2)
);

INSERT INTO employees
(employee_id, employee_name, department_id, salary)
VALUES
(1, 'Aarav', 10, 35000.00),
(2, 'Meera', 20, 48000.00),
(3, 'Kabir', 10, 42000.00),
(4, 'Diya', 30, 55000.00),
(5, 'Rohan', 20, 30000.00);

Verify the sample records:

SELECT *
FROM employees
ORDER BY employee_id;

9. Example 1: Reading Employees One by One

This procedure fetches employee IDs and names one row at a time. It returns each fetched record as a separate result set for demonstration. A single SELECT statement is generally better for ordinary reporting.

DELIMITER //

CREATE PROCEDURE list_employees_with_cursor()
BEGIN
    DECLARE v_employee_id INT;
    DECLARE v_employee_name VARCHAR(100);
    DECLARE v_done BOOLEAN DEFAULT FALSE;

    DECLARE employee_cursor CURSOR FOR
        SELECT employee_id, employee_name
        FROM employees
        ORDER BY employee_id;

    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET v_done = TRUE;

    OPEN employee_cursor;

    employee_loop: LOOP
        FETCH employee_cursor
        INTO v_employee_id, v_employee_name;

        IF v_done THEN
            LEAVE employee_loop;
        END IF;

        SELECT v_employee_id AS employee_id,
               v_employee_name AS employee_name;
    END LOOP;

    CLOSE employee_cursor;
END //

DELIMITER ;

CALL list_employees_with_cursor();

9.1 Explanation

• The cursor selects employee IDs and names.
• FETCH retrieves one row at a time.
• The handler sets the completion flag when no rows remain.
• The IF statement exits the loop when the flag is TRUE.
• The cursor closes after the loop finishes.

10. Example 2: Classifying Employees by Salary

Suppose a company wants to classify each employee as meeting or falling below a salary threshold of 40000.

DELIMITER //

CREATE PROCEDURE classify_employee_salaries()
BEGIN
    DECLARE v_employee_id INT;
    DECLARE v_employee_name VARCHAR(100);
    DECLARE v_salary DECIMAL(10,2);
    DECLARE v_status VARCHAR(30);
    DECLARE v_done BOOLEAN DEFAULT FALSE;

    DECLARE employee_cursor CURSOR FOR
        SELECT employee_id, employee_name, salary
        FROM employees
        ORDER BY employee_id;

    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET v_done = TRUE;

    OPEN employee_cursor;

    employee_loop: LOOP
        FETCH employee_cursor
        INTO v_employee_id, v_employee_name, v_salary;

        IF v_done THEN
            LEAVE employee_loop;
        END IF;

        IF v_salary >= 40000 THEN
            SET v_status = 'Meets Threshold';
        ELSE
            SET v_status = 'Below Threshold';
        END IF;

        SELECT v_employee_id AS employee_id,
               v_employee_name AS employee_name,
               v_salary AS salary,
               v_status AS salary_status;
    END LOOP;

    CLOSE employee_cursor;
END //

DELIMITER ;

CALL classify_employee_salaries();

10.1 Expected Classification

In many SQL clients, the SELECT statement inside the loop produces a separate result set for each employee. In a production procedure, consider writing the results into a table and returning them in one final SELECT statement.

11. Example 3: Processing Employees by Department

A cursor can also process a filtered subset of records. The following procedure accepts a department ID as an input parameter.

DELIMITER //

CREATE PROCEDURE process_department_employees(
    IN p_department_id INT
)
BEGIN
    DECLARE v_employee_id INT;
    DECLARE v_employee_name VARCHAR(100);
    DECLARE v_salary DECIMAL(10,2);
    DECLARE v_done BOOLEAN DEFAULT FALSE;

    DECLARE employee_cursor CURSOR FOR
        SELECT employee_id, employee_name, salary
        FROM employees
        WHERE department_id = p_department_id
        ORDER BY employee_id;

    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET v_done = TRUE;

    OPEN employee_cursor;

    employee_loop: LOOP
        FETCH employee_cursor
        INTO v_employee_id, v_employee_name, v_salary;

        IF v_done THEN
            LEAVE employee_loop;
        END IF;

        SELECT v_employee_id AS employee_id,
               v_employee_name AS employee_name,
               v_salary AS salary;
    END LOOP;

    CLOSE employee_cursor;
END //

DELIMITER ;

CALL process_department_employees(10);

Explanation: The procedure accepts a department ID, selects matching employees, and fetches them individually in ascending employee ID order. With the sample data, department 10 contains Aarav and Kabir.

12. Example 4: Calculating a Total Using a Cursor

This example demonstrates how a variable can accumulate values as each row is fetched. It is useful for learning cursor logic, although a SUM() query is normally preferable for calculating a straightforward total.

DELIMITER //

CREATE PROCEDURE calculate_salary_total_with_cursor()
BEGIN
    DECLARE v_salary DECIMAL(10,2);
    DECLARE v_total DECIMAL(12,2) DEFAULT 0;
    DECLARE v_done BOOLEAN DEFAULT FALSE;

    DECLARE salary_cursor CURSOR FOR
        SELECT salary
        FROM employees;

    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET v_done = TRUE;

    OPEN salary_cursor;

    salary_loop: LOOP
        FETCH salary_cursor INTO v_salary;

        IF v_done THEN
            LEAVE salary_loop;
        END IF;

        SET v_total = v_total + COALESCE(v_salary, 0);
    END LOOP;

    CLOSE salary_cursor;

    SELECT v_total AS total_salary;
END //

DELIMITER ;

CALL calculate_salary_total_with_cursor();

The expected total for the sample data is 210000.00.

The simpler set-based alternative is:

SELECT COALESCE(SUM(salary), 0) AS total_salary
FROM employees;

13. Understanding the NOT FOUND Handler

A FETCH operation raises a no-data condition when the cursor reaches the end of its result set. The NOT FOUND handler allows the program to respond to this condition.

13.1 Standard Loop Pattern

DECLARE v_done BOOLEAN DEFAULT FALSE;

DECLARE employee_cursor CURSOR FOR
    SELECT employee_id
    FROM employees;

DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET v_done = TRUE;

OPEN employee_cursor;

employee_loop: LOOP
    FETCH employee_cursor INTO v_employee_id;

    IF v_done THEN
        LEAVE employee_loop;
    END IF;

    -- Process the fetched row here.
END LOOP;

CLOSE employee_cursor;

13.2 Why Check the Flag Immediately?

When FETCH reaches the end of the result set, no new valid row has been retrieved. Checking the flag immediately prevents the program from treating variable contents as a successfully fetched row.

13.3 Handling Empty Result Sets

If the cursor query returns no rows, the first FETCH activates the handler. The flag becomes TRUE, the loop exits, and the cursor closes without processing a nonexistent record.

13.4 Handler Scope Considerations

A NOT FOUND condition can also be raised by certain other statements inside the same handler scope. If a procedure needs to distinguish the cursor's end-of-data condition from another no-data condition, use a nested BEGIN ... END block with an appropriately scoped handler.

14. Declaration Order and Scope

MySQL requires declarations inside a BEGIN ... END block to appear in a specific order.

Keeping declarations in the correct order prevents syntax errors. Clear variable names also help distinguish local variables from table columns.

15. Cursors vs Set-Based SQL

Set-based SQL is often faster and simpler because the database can process many matching rows through a single statement. Cursors should be used when individual procedural processing provides a genuine benefit.

15.1 Example: Cursor vs UPDATE

Suppose all employees earning less than 40000 must receive a 5% salary increase. If the same rule applies to every matching record, a cursor is unnecessary.

UPDATE employees
SET salary = salary * 1.05
WHERE salary < 40000;

This statement updates all matching employees directly. A cursor would add complexity without providing a clear advantage for this simple requirement.

16. Common Cursor Errors and Troubleshooting

17. Best Practices for Using Cursors

• Prefer set-based SQL: Check whether a single SQL statement can solve the problem first.
• Limit selected rows: Filter the cursor query and select only the required columns.
• Use meaningful names: Name cursors, variables, and loops according to their purpose.
• Follow declaration rules: Declare variables, conditions, cursors, and handlers in the correct order.
• Handle end-of-data correctly: Use a NOT FOUND handler and check the flag immediately after FETCH.
• Close cursors: Close the cursor after processing finishes.
• Specify ordering when necessary: Use ORDER BY if processing order affects the business logic.
• Test empty results: Make sure the procedure works when no records match the query.
• Consider NULL values: Design comparisons and calculations to handle nullable data.
• Consider transaction safety: For data-modifying procedures, review transaction boundaries, locking, and error handling.
• Test realistic data volumes: Measure performance before using a cursor in production.

18. Mini Project: Employee Salary Review

Project objective: Create a stored procedure that reads employees individually and classifies their salaries.

18.1 Project Requirements

• Read employee ID, name, and salary from the employees table.
• Classify salaries below 35000 as "Needs Review".
• Classify salaries from 35000 to below 50000 as "Standard".
• Classify salaries of 50000 or above as "High Salary".
• Use a cursor to fetch the employee records one at a time.
• Exit the loop when no rows remain.
• Close the cursor after processing.

18.2 Sample Solution

DELIMITER //

CREATE PROCEDURE review_employee_salaries()
BEGIN
    DECLARE v_employee_id INT;
    DECLARE v_employee_name VARCHAR(100);
    DECLARE v_salary DECIMAL(10,2);
    DECLARE v_category VARCHAR(30);
    DECLARE v_done BOOLEAN DEFAULT FALSE;

    DECLARE employee_cursor CURSOR FOR
        SELECT employee_id, employee_name, salary
        FROM employees
        ORDER BY employee_id;

    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET v_done = TRUE;

    OPEN employee_cursor;

    employee_loop: LOOP
        FETCH employee_cursor
        INTO v_employee_id, v_employee_name, v_salary;

        IF v_done THEN
            LEAVE employee_loop;
        END IF;

        IF v_salary < 35000 THEN
            SET v_category = 'Needs Review';
        ELSEIF v_salary < 50000 THEN
            SET v_category = 'Standard';
        ELSE
            SET v_category = 'High Salary';
        END IF;

        SELECT v_employee_id AS employee_id,
               v_employee_name AS employee_name,
               v_salary AS salary,
               v_category AS salary_category;
    END LOOP;

    CLOSE employee_cursor;
END //

DELIMITER ;

CALL review_employee_salaries();

This project practices cursor declaration, sequential fetching, conditional logic, end-of-data handling, and cursor cleanup. For production reporting, consider storing the classifications and returning them in one final result set instead of returning a separate result set for each employee.

19. Practice Exercises

1. Create a cursor that reads employee IDs and names in ascending order.
2. Create a stored procedure that processes only employees in department 20.
3. Use a cursor to count employees whose salaries exceed 40000.
4. Create a cursor that classifies employees using three salary ranges of your choice.
5. Test a cursor when its SELECT query returns no rows.
6. Rewrite a cursor-based bulk update using a single UPDATE statement.
7. Compare the complexity of a cursor-based total with a SUM() query.

20. Interview Questions and Answers

Q1. What is a cursor in MySQL?

A cursor is a stored-program mechanism that fetches rows from a SELECT result sequentially, allowing individual processing.

Q2. Why are cursors used?

They are useful when each row requires procedural decisions, sequential calculations, or individual handling that is difficult to express with set-based SQL.

Q3. What are the main cursor statements?

DECLARE, OPEN, FETCH, and CLOSE are the four main cursor statements.

Q4. What does DECLARE CURSOR do?

It defines the SELECT query associated with a cursor.

Q5. What does OPEN do?

It opens the declared cursor so its rows can be fetched.

Q6. What does FETCH do?

It retrieves the next row and assigns its column values to receiving variables.

Q7. What is a NOT FOUND handler?

It handles a no-data condition, commonly setting a flag when FETCH reaches the end of the result set.

Q8. Why should a cursor be closed?

CLOSE ends the cursor's use and releases its associated resources when processing is complete.

Q9. Are MySQL cursors scrollable?

No. MySQL stored-program cursors are non-scrollable and are traversed in one direction.

Q10. Can a cursor directly update its current row?

No. MySQL cursors are read-only. Data modifications must be performed using appropriate SQL statements.

Q11. Are cursors always more efficient than normal SQL statements?

No. Set-based SQL is usually more efficient for bulk operations and aggregations. Cursors are appropriate when individual processing is genuinely needed.

Q12. Why must FETCH variables match the SELECT columns?

Each selected column must have a corresponding receiving variable in the same order.

Q13. What happens when a cursor query returns no rows?

The first FETCH raises a no-data condition. A suitable handler can set a flag that causes the loop to exit safely.

Q14. Why is ORDER BY useful in a cursor query?

It specifies the row order when the sequence of processing matters to the application.

Q15. Where can I learn more about MySQL cursors?

Consult the official MySQL Reference Manual for cursor syntax, restrictions, and examples.

21. Quick Revision Checklist

• A cursor processes query results one row at a time.
• MySQL cursors are read-only and non-scrollable.
• Declare local variables before cursor declarations.
• Declare cursors before handlers.
• Open the cursor before fetching rows.
• Match FETCH variables to the SELECT columns and their order.
• Use a NOT FOUND handler to detect the end of the result set.
• Check the completion flag immediately after FETCH.
• Close the cursor after processing.
• Prefer set-based SQL for operations that can be performed on an entire set of rows.

22. Conclusion

Cursors are a useful feature of MySQL stored-program development when a task requires individual processing of query results. Understanding DECLARE, OPEN, FETCH, CLOSE, and NOT FOUND handling helps you develop reliable cursor loops. However, cursors introduce procedural complexity and can be slower than set-based SQL, so use them when row-by-row processing offers a real advantage.

Continue learning through JustAcademy's MySQL Training Course, or register for a course demo.

Official Documentation: MySQL 8.4 Reference Manual — Cursors and Cursor FETCH Statement.